using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace DAO
{
   public class DataProvider
    {
        public static string con = @"provider=microsoft.Ace.OleDb.12.0;Data Source= quanlythuvien.mdb";
       static OleDbConnection connection;

       #region @
       public static OleDbConnection ketnoi()
       {
           OleDbConnection con = new OleDbConnection();
           string strcon = @"provider=microsoft.Ace.OleDb.12.0;Data Source= quanlythuvien.mdb";
           con.ConnectionString = strcon;
           if (con.State == ConnectionState.Closed)
               con.Open();
           return con;
       }

       #endregion
       public static void OpenConnection()
        {
           
                connection = new OleDbConnection(con);
                connection.Open();          
        }
        public static void CloseConnection()
        {
            if (connection != null)
            {
                connection.Close();
            }
        }
       public static DataTable GetDataTable(string tblName,OleDbConnection con )
      {
          string sql;
          DataTable tbl = new DataTable();
          DataSet ds = new DataSet();
          OleDbDataAdapter da = new OleDbDataAdapter();
          OleDbCommand cmd = new OleDbCommand();
          sql = "select * from " + tblName;
          cmd.Connection = con;
          cmd.CommandType = CommandType.Text;
          cmd.CommandText = sql;
          da.SelectCommand = cmd;
          da.Fill(ds, tblName);
          tbl = ds.Tables[0];
          return tbl;
      }
       public static bool ExecuteNonQuery(string sql)
       {
           try
           {
               OpenConnection();
               OleDbCommand cmd = new OleDbCommand(sql, connection);
               cmd.ExecuteNonQuery();
               CloseConnection();
               return true;
           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
       
    }
       public static DataTable laythongtinsach(int strmasach, OleDbConnection connection)
       {
           string tenbanglay;
           DataTable laybang = new DataTable();
           OleDbDataAdapter da = new OleDbDataAdapter();
           OleDbCommand cmd = new OleDbCommand();
           DataSet ds = new DataSet();
           tenbanglay = "select * from Sach where MaSach=" + strmasach + "";
           cmd.Connection = connection;
           cmd.CommandType = CommandType.Text;
           cmd.CommandText = tenbanglay;
           da.SelectCommand = cmd;

           da.Fill(ds,"Sach");
           laybang = ds.Tables[0];
           return laybang;
       }
       public static DataTable laythongtindocgia(string MaDG, OleDbConnection connection)
       {
           string tenbanglay;
           DataTable laybang = new DataTable();
           OleDbDataAdapter da = new OleDbDataAdapter();
           OleDbCommand cmd = new OleDbCommand();
           DataSet ds = new DataSet();
           tenbanglay = "select * from Docgia where MaDG='" + MaDG + "'";
           cmd.Connection = connection;
           cmd.CommandType = CommandType.Text;
           cmd.CommandText = tenbanglay;
           da.SelectCommand = cmd;

           da.Fill(ds, "Docgia");
           laybang = ds.Tables[0];
           return laybang;
       }
    }
}
